﻿using System;
using System.Collections.Generic;
using System.Text;
using DotNet.Model;
using DotNet.Common;
using DotNet.Data.Factory;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
namespace DotNet.Data
{
	public class QuestionDal
	{
		IDBHelp db = DBHelp.Create();
		Hashtable htSyn = Hashtable.Synchronized(new Hashtable());
		StringBuilder strSql = new StringBuilder();

		#region 是否存在该记录
		/// <summary>
		/// 是否存在该记录
		/// </summary>
		/// <param name="QuestionID">标识列ID</param>
		public bool Exists(int QuestionID)
		{
			htSyn.Clear();
			strSql.Remove(0, strSql.Length);
			strSql.Append("select count(1) from [Question] where QuestionID=@questionid ");
			htSyn.Add("@questionid", QuestionID);
			return db.IsExists(strSql.ToString(), htSyn);
		}
		#endregion

		#region 根据父id得到该表数据列表---郑同飞
		/// <summary>
        /// 根据父id得到该表数据列表---郑同飞
		/// </summary>
		public IList<Question> GetModelByFid(int fid)
		{
			strSql.Remove(0, strSql.Length);
            htSyn.Clear();
            strSql.Append("Select [QuestionState],[QuestionClassID],[PostTime],[QuestionID],[UserName],[ProductID],[Description],[Tel],[Title],[fid],[IsSend] FROM [Question] where fid=@fid order by posttime asc");
            htSyn.Add("fid", fid);
		    return getQuestionBySql(strSql.ToString(),htSyn);
		}
		#endregion

        #region 根据问题类别id得到问题的数量---郑同飞
        /// <summary>
        /// 根据问题类别id得到问题的数量---郑同飞
        /// </summary>
        /// <param name="questionClassID">问题类别id</param>
        /// <returns></returns>
        public int getCountByQcID(int questionClassID)
        {
            object objCount = null;
            int i = 0;
            htSyn.Clear();
            htSyn.Add("@questionclassid", questionClassID);
            objCount = db.ExecuteScalar("select count(1) from [Question] Where QuestionClassID=@questionclassid and Fid=0",htSyn);
            if (objCount != null)
            {
                i = Convert.ToInt32(objCount);
            }
            return i;
        }
        #endregion

        #region 根据标识列查询实体
        /// <summary>
		/// 根据标识列查询实体
		/// </summary>
		/// <param name="QuestionID">标识列ID</param>
        /// <param name="fid">问题父id</param>
		/// <returns></returns>
		public Question GetModel(int questionid)
		{
			htSyn.Clear();
			strSql.Remove(0, strSql.Length);
            strSql.Append("select [QuestionState],[QuestionClassID],[PostTime],[QuestionID],[UserName],[ProductID],[Description],[Tel],[Title],[fid],[IsSend] FROM [Question] ");
			strSql.Append(" where QuestionID=@questionid");
			htSyn.Add("@QuestionID", questionid);            
			DataTable dt = db.GetDataTable(strSql.ToString(),htSyn);
			Question questionModel = new Question();
			if (dt.Rows.Count > 0)
			{
				questionModel.questionState=Convert.ToBoolean(dt.Rows[0]["QuestionState"]);
				questionModel.postTime=Convert.ToDateTime(dt.Rows[0]["PostTime"]);
                questionModel.userName = dt.Rows[0]["UserName"].ToString();
				questionModel.productID=Convert.ToInt32(dt.Rows[0]["ProductID"]);
				questionModel.description=dt.Rows[0]["Description"].ToString();
				questionModel.tel=dt.Rows[0]["Tel"].ToString();
				questionModel.title=dt.Rows[0]["Title"].ToString();
                questionModel.fid=Convert.ToInt32(dt.Rows[0]["fid"]);
                questionModel.questionID = Convert.ToInt32(dt.Rows[0]["QuestionID"]);
                questionModel.questionClassID = Convert.ToInt32(dt.Rows[0]["QuestionClassID"]);
                questionModel.isSend = Convert.ToBoolean(dt.Rows[0]["IsSend"]);
			}
			return questionModel;
		}
		#endregion

		#region 修改信息
		/// <summary>
		/// 修改信息
		/// </summary>
		/// <param name="Question">实体</param>
		/// <returns></returns>
		public bool Update(Question questionModel)
		{
			htSyn.Clear();
			strSql.Remove(0, strSql.Length);
			strSql.Append("update [Question] set ");
			strSql.Append("[QuestionState]=@questionState,");
			strSql.Append("[PostTime]=@postTime,");
            strSql.Append("[UserName]=@username,");
			strSql.Append("[ProductID]=@productID,");
			strSql.Append("[Description]=@description,");
			strSql.Append("[Tel]=@tel,");
			strSql.Append("[Title]=@title,");
            strSql.Append("[fid]=@fid,");
            strSql.Append("[QuestionClassID]=@questionclassid,");
            strSql.Append("[IsSend]=@issend");
			strSql.Append(" where QuestionID=@questionid ");
			htSyn.Add("@questionstate", questionModel.questionState);
			htSyn.Add("@posttime", questionModel.postTime);
			htSyn.Add("@questionid", questionModel.questionID);
            htSyn.Add("@username", questionModel.userName);
			htSyn.Add("@productid", questionModel.productID);
			htSyn.Add("@description", questionModel.description);
			htSyn.Add("@tel", questionModel.tel);
			htSyn.Add("@title", questionModel.title);
            htSyn.Add("@fid",questionModel.fid);
            htSyn.Add("@questionclassid",questionModel.questionClassID);
            htSyn.Add("@issend",questionModel.isSend);
			if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
			{
				return true;
			}
			return false;
		}
		#endregion

        #region ajax批量修改---郑同飞
        /// <summary>
        /// ajax批量修改---郑同飞
        /// </summary>
        /// <param name="qid"></param>
        /// <param name="qclassid"></param>
        /// <returns></returns>
        public bool Update(string[] qid,string qclassid)
        {
            strSql.Remove(0, strSql.Length);
            htSyn.Clear();
            strSql.Append("update [Question] set");
            strSql.Append(" QuestionClassID=@questionclassid");
            strSql.Append(" where ");
            foreach (string str in qid)
            {
                strSql.AppendFormat(" QuestionID={0} or", str);
            }
            strSql.Remove(strSql.Length - 1, 1);
            strSql.Remove(strSql.Length - 1, 1);
            htSyn.Add("@questionclassid", qclassid);
            if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 删除信息
        /// <summary>
		/// 删除信息
		/// </summary>
		/// <param name="QuestionID">标识列ID</param>
		/// <returns></returns>
		public bool Delete(int QuestionID)
		{
			htSyn.Clear();
			strSql.Remove(0, strSql.Length);
			strSql.Append("delete from [Question] where QuestionID=@questionid");
			htSyn.Add("questionid", QuestionID);
			if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
			{
				return true;
			}
			return false;
		}
		#endregion

        #region 根据用户名删除信息--ztf
        /// <summary>
        /// 根据用户名删除信息--ztf
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public bool Delete(string userName)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("delete from [Question] where UserName=@username");
            htSyn.Add("username", userName);
            if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 根据父id删除问题
        /// <summary>
        /// 根据父id删除问题
        /// </summary>
        /// <param name="fid">父id</param>
        /// <returns></returns>
        public bool DeleteByFid(int fid) 
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("delete from [Question] where fid=@fid");
            htSyn.Add("@fid", fid);
            if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 添加信息
        /// <summary>
		/// 添加信息
		/// </summary>
		/// <param name="Question">实体</param>
		/// <returns></returns>
		public bool Insert(Question questionModel)
		{
			htSyn.Clear();
			strSql.Remove(0, strSql.Length);
            strSql.Append("insert into [Question]([QuestionState],[QuestionClassID],[PostTime],[UserName],[ProductID],[Description],[Tel],[Title],[fid],[IsSend]) values (@questionstate,@questionclassid,@posttime,@username,@productid,@description,@tel,@tit,@fid,@issend)");
            htSyn.Add("@questionstate", questionModel.questionState);
            htSyn.Add("@posttime", questionModel.postTime);
            htSyn.Add("@username", questionModel.userName);
            htSyn.Add("@productid", questionModel.productID);
            htSyn.Add("@tel", questionModel.tel);
            htSyn.Add("@description", questionModel.description);
            htSyn.Add("@tit", questionModel.title);
            htSyn.Add("@fid", questionModel.fid);
            htSyn.Add("@questionclassid",questionModel.questionClassID);
            htSyn.Add("@issend",questionModel.isSend);
			if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
			{
				return true;
			}
			return false;
		}
		#endregion

		#region 根据SQL语句获取集合
		/// <summary>
		///根据SQL语句获取集合
		/// </summary>
		/// <param name="strSql">sql语句</param>
        /// <param name="ht">参数</param>
        public IList<Question> getQuestionBySql(string strSql)
		{
			IList<Question> list = new List<Question>();            
			DataTable dt = db.GetDataTable(strSql);
			if (dt.Rows.Count > 0)
			{
				foreach (DataRow dr in dt.Rows)
				{
					Question questionModel = new Question();
					questionModel.questionState= Convert.ToBoolean(dr["QuestionState"]);
					questionModel.postTime= Convert.ToDateTime(dr["PostTime"]);
					questionModel.questionID= Convert.ToInt32(dr["QuestionID"]);
					questionModel.userName=dr["UserName"].ToString();
					questionModel.productID= Convert.ToInt32(dr["ProductID"]);
					questionModel.description=dr["Description"].ToString();
					questionModel.tel=dr["Tel"].ToString();
					questionModel.title=dr["Title"].ToString();
                    questionModel.fid=Convert.ToInt32(dr["fid"]);
                    questionModel.questionClassID = Convert.ToInt32(dr["QuestionClassID"]);
                    questionModel.isSend = Convert.ToBoolean(dr["IsSend"]);
					list.Add(questionModel);
				}
			}
			return list;
		}
		#endregion

        #region 根据SQL语句获取集合
        /// <summary>
        ///根据SQL语句获取集合
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="ht">参数</param>
        public IList<Question> getQuestionBySql(string strSql,Hashtable ht)
        {
            IList<Question> list = new List<Question>();
            DataTable dt = db.GetDataTable(strSql,ht);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    Question questionModel = new Question();
                    questionModel.questionState = Convert.ToBoolean(dr["QuestionState"]);
                    questionModel.postTime = Convert.ToDateTime(dr["PostTime"]);
                    questionModel.questionID = Convert.ToInt32(dr["QuestionID"]);
                    questionModel.userName = dr["UserName"].ToString();
                    questionModel.productID = Convert.ToInt32(dr["ProductID"]);
                    questionModel.description = dr["Description"].ToString();
                    questionModel.tel = dr["Tel"].ToString();
                    questionModel.title = dr["Title"].ToString();
                    questionModel.fid = Convert.ToInt32(dr["fid"]);
                    questionModel.questionClassID = Convert.ToInt32(dr["QuestionClassID"]);
                    questionModel.isSend = Convert.ToBoolean(dr["IsSend"]);
                    list.Add(questionModel);
                }
            }
            return list;
        }
        #endregion

        #region 问题分页---郑同飞
        /// <summary>
        /// 问题分页---郑同飞
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="userName"></param>
        /// <param name="questionClassID"></param>
        /// <param name="productid"></param>
        /// <param name="questionstate"></param>
        /// <returns></returns>
        public DataTable pagingForQuestion(int pageIndex, int pageSize,string userName,int questionClassID,string productid,int? questionstate) 
        {
            strSql.Remove(0,strSql.Length);
            strSql.Append(" and Fid=0");
            if (userName!="")
            {
                strSql.AppendFormat(" and UserName='{0}'",userName);
            }
            if (questionClassID!=0)
            {
                strSql.AppendFormat(" and QuestionClassID={0}",questionClassID);
            }
            if (productid!="0"&&productid!="")
            {
                strSql.AppendFormat(" and ProductID='{0}'",productid);
            }
            if (questionstate!=null)
            {
                strSql.AppendFormat(" and QuestionState={0}", questionstate);               
            }
            return db.GetMPageDataTable(pageIndex, pageSize, "[QuestionState],[QuestionClassID],[PostTime],[QuestionID],[UserName],[ProductID],[Description],[Tel],[Title],[fid],[IsSend]", "Question", strSql.ToString(), "PostTime", true);
        }
        #endregion

        #region 获取问题数量---郑同飞
        /// <summary>
        /// 获取问题数量---郑同飞
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="questionClassID"></param>
        /// <param name="productId"></param>
        /// <param name="questionState"></param>
        /// <returns></returns>
        public int getRecordCount(string userName,int questionClassID,String productId,int? questionState) 
        {
            strSql.Remove(0, strSql.Length); 
            htSyn.Clear();
            object objRecordCount = null;
            int i = 0;
            strSql.Append("select count(1) from [Question] Where fid=0");
            if (userName!="")
            {
                strSql.Append(" and UserName=@username");
                htSyn.Add("@username",userName);
            }
            if (questionClassID!=0)
            {
                strSql.Append(" and QuestionClassID=@questionclassid");
                htSyn.Add("@questionclassid", questionClassID);
            }
            if (productId!="")
            {
                strSql.Append(" and ProductID=@productid");
                htSyn.Add("@productid",productId);
            }
            if (questionState!=null)
            {
                strSql.Append(" and QuestionState=@questionState");
                htSyn.Add("@questionState", questionState);
            }
            objRecordCount = db.ExecuteScalar(strSql.ToString(),htSyn);
            if (objRecordCount != null)
            {
                i = Convert.ToInt32(objRecordCount);
            }
            return i;
        }        
        #endregion

        #region 根据问题id获取最近提问---郑同飞
        /// <summary>
        /// 根据问题id获取最近提问---郑同飞
        /// </summary>
        /// <param name="qidList"></param>
        /// <returns></returns>
        public IList<Question> getLastQuestion(IList<int> qidList)
        {            
            strSql.Remove(0, strSql.Length);
            htSyn.Clear();
            string str = "";
            foreach (int qid in qidList)
            {
                str += qid + ",";
            }
            str=str.Remove(str.Length - 1, 1);
            strSql.AppendFormat("select [QuestionState],[QuestionClassID],[PostTime],[QuestionID],[UserName],[ProductID],[Description],[Tel],[Title],[fid],[IsSend] FROM [Question] where PostTime>(select max(replytime) from answer where questionid in ({0})) and questionid in ({0})",str);
            return getQuestionBySql(strSql.ToString());
        }
        #endregion

    }
}
